Assets_by_Program <- read_excel("3 States Comparison_my work.xlsx", sheet = "Assets by Program")
Total_Withdrawals <- read_excel("3 States Comparison_my work.xlsx", sheet = "Total Withdrawals")
Funded_Accts <- read_excel("3 States Comparison_my work.xlsx", sheet = "Funded Accts")
Accts_with_Withdrawal <- read_excel("3 States Comparison_my work.xlsx", sheet = "Accts with Withdrawal")
Avg_Defferral_Rate <- read_excel("3 States Comparison_my work.xlsx", sheet = "Avg Defferral Rate")
Avg_Contrib_Amt <- read_excel("3 States Comparison_my work.xlsx", sheet = "Avg Contrib Amt")
Opt_Out_Rate <- read_excel("3 States Comparison_my work.xlsx", sheet = "Opt Out Rate")
Average_Funded_Account_Balance <- read_excel("3 States Comparison_my work.xlsx", sheet = "Average Funded Account Balance")
Registered_Employers <- read_excel("3 States Comparison_my work.xlsx", sheet = "Registered Employers")
Adding_Employee_Data <- read_excel("3 States Comparison_my work.xlsx", sheet = "Adding Employee Data")
Submitting_Payroll <- read_excel("3 States Comparison_my work.xlsx", sheet = "Submitting Payroll")
# Dynamic graph
plot <-ggplot(Assets_by_Program, aes(x = Date)) +
geom_line(aes(y = OregonSaves, color = "OregonSaves"), size = 1) +
geom_line(aes(y = `IL Secure Choice`, color = "IL Secure Choice"), size = 1) +
geom_line(aes(y = CalSavers, color = "CalSavers"), size = 1) +
labs(
title = "State Retirement Programs - Total Assets",
x = "Date",
y = "Total Assets",
color = "Program"
) +
scale_y_continuous(labels = scales::dollar_format(prefix = "$")) +
theme_minimal() +
theme(legend.position = "top")
ggplotly(plot)
Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.
library(tidyr)
your_data <- Assets_by_Program %>%
arrange(Date) %>%
mutate(
OregonSaves_Growth = (OregonSaves / lag(OregonSaves)) - 1,
`IL Secure Choice_Growth` = (`IL Secure Choice` / lag(`IL Secure Choice`)) - 1,
CalSavers_Growth = (CalSavers / lag(CalSavers)) - 1
)
growth_melted <- your_data %>%
select(Date, OregonSaves, `IL Secure Choice`, CalSavers,
OregonSaves_Growth, `IL Secure Choice_Growth`, CalSavers_Growth) %>%
pivot_longer(cols = -Date, names_to = "Program", values_to = "Value")
# Filter for lines related to assets (exclude _Growth)
assets_data <- growth_melted %>%
filter(!grepl("_Growth", Program))
assets_data2 <- growth_melted %>%
filter(grepl("_Growth", Program)) %>%
rename(Value2= Value)
data <- cbind(assets_data, assets_data2)
# Create the Plotly plot
plot_ly(data, x = ~Date, y = ~Value, color = ~Program, text = ~paste("Growth Rate: ", round(Value2, digits = 4)*100, "%")) %>%
add_lines(line = list(width = 3)) %>%
layout(
title = "State Retirement Programs - Total Assets",
xaxis = list(title = "Date"),
yaxis = list(title = "Value"),
hovermode = "x unified"
)